#!/usr/bin/env python
# coding:utf-8


import logging
from datatabletransfer import DataTableTransfer
from fieldsmap import *


class FMFIndex(DataTableTransfer):
    """
    基金主要财务指标(TQ_FD_MAININDEX) --> 基金主要财务指标(fund_main_financial_index)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,ENDDATE as end_date ,REPORTTYPE as report_type from TQ_FD_MAININDEX ;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code,replace(f.end_date,'-','') as end_date,report_type
        from fund_main_financial_index f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID,PUBLISHDATE,BEGINDATE,ENDDATE,REPORTTYPE,ACCSTACODE,
    # ACCSTANAME,CUR,PROFITPERIOD,FDNETPROPER,AVGFDPROPER,UNFDNETINCPER,FDDISTNETINC,UNFDDISTNETINC,FINAFDTOTASSET,
    # FINAFDASSETNAV,FINAUNFDASSNAV,FDASSETNAVYIELD,FDDISTYIELD,FDAVGNAVYIELD,FDNAVGROWRATE,FDACCGROWATE,ISSTAT
    # from TQ_FD_MAININDEX limit %s, 1000;''')

    TABLE = 'fund_main_financial_index'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'end_date':False, 'report_type':True}   # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'begin_date': None,
        'end_date': None,
    }

    MAP_FIELDS = FMFIndexMap
    MAP_VALUES = FMFIndexValueMap


class FundBalanceSheet(DataTableTransfer):
    """
    基金资产负债表(旧准则)(TQ_FD_BSHEET) --> 基金资产负债表(旧准则)(fund_balance_sheet)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,REPORTDATE as end_date ,1916002 as report_type, ACCSTACODE as
    accounting_standard_code from TQ_FD_BSHEET ;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code,replace(f.end_date,'-','') as end_date,1916002 as report_type,
     accounting_standard_code  from fund_balance_sheet f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID,PUBLISHDATE,REPORTDATE, 1916002 as REPORT_TYPE,
    # ACCSTACODE,ACCSTANAME,CUR,CASH,DEPOINBANK,LIQUPROV,TRADMARG,STKINVEMKTCAP,STKINVECOST,STKIVALA,BDINVEMKTCAP,BDINVECOST,
    # BDINVEVALAPP,CONVBDCOST,CONVBDVALAPP,WRTINVE,WRTCOST,NATDINVEMKTC,NATDINVECOST,NATDVALAPP,BUYREVBDCOST,OTHERINVECOST,
    # OTHERINVEVALAPP,RECESTKL,TOTINTE,DIVIDRECE,DIVIBKIN,NEWSSUBP,ACCORECE,ALLOWRT,PREPEXPE,OTHERRECEACCT,FDTOTASSET,
    # PAYBLIQU,PAYACC,PAYREDF,PAYFDMANF,MERITPAY,PAYFDTRUS,PAYSSERV,PAYSUSEF,COMMPAYA,INTEPAYA,PAYINCO,PAYALLO,
    # SELLBACKBDINCO,PAYTOTA,UNPATAXES,ACCREXPE,OTHERPAY,TOTALLIAB,FDTOTUNIT,NETINCO,UNREAVALA,UNALNETI,ASSETNAV,LIABTOTH,
    # ISSTAT from TQ_FD_BSHEET limit %s, 1000;''')

    TABLE = 'fund_balance_sheet'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'end_date':False, 'report_type':False, 'accounting_standard_code':True}
    # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'end_date': None,
    }

    MAP_FIELDS = FundBalanceSheetMap
    MAP_VALUES = FundBalanceSheetValueMap


class FundBalanceSheetNew(DataTableTransfer):
    """
    基金资产负债表(新准则)(TQ_FD_BSHEETNEW) --> 基金资产负债表(新准则)(fund_balance_sheet_new)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,REPORTDATE as end_date ,REPORTTYPE as report_type, ACCSTACODE as
       accounting_standard_code from TQ_FD_BSHEETNEW ;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code,replace(f.end_date,'-','') as end_date, report_type,
        accounting_standard_code  from fund_balance_sheet_new f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID,PUBLISHDATE,REPORTDATE,REPORTTYPE,ACCSTACODE,ACCSTANAME,
    # CUR,DEPOINBANK,SETTRESEDEPO,KEPTMARG,TRADFINASSET,STKINVE,FDINVE,BDINVE,ASSESECI,DERIFINAASSET,PURCRESAASSET,
    # RECESECU,INTERECE,DIVIDRECE,RECSACCA,OTHERASSE,TOTASSET,SHORTTERMBORR,TRADFINLIAB,DERILIAB,SELLREPASSE,PAYSLIQU,
    # PAYACC,PAYFDMANF,PAYFDTRUS,PAYSSERV,PAYTFEE,TAXESPAYA,INTEPAYA,PROFPAYA,OTHERLIABI,TOTLIAB,PAIDFD,UNDIPROF,
    # TOTALOWNEREQUI,TOTALLIABSHAREQUI,ISSTAT,MEMO from TQ_FD_BSHEETNEW limit %s, 1000;''')

    TABLE = 'fund_balance_sheet_new'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'end_date':False, 'report_type':True, 'accounting_standard_code':True}
    # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'end_date': None,
    }

    MAP_FIELDS = FundBalanceSheetNewMap
    MAP_VALUES = FundBalanceSheetNewValueMap


class FundIncomeStatement(DataTableTransfer):
    """
    基金利润表(旧准则)(TQ_FD_PROFIT) --> 基金利润表(旧准则)(fund_income_statement)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,BEGINDATE as begin_date ,ENDDATE as end_date,REPORTTYPE as report_type
     from TQ_FD_PROFIT ;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code,replace(f.begin_date,'-','') as begin_date,
    replace(f.end_date,'-','') as end_date, report_type from fund_income_statement f
     join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID,PUBLISHDATE,BEGINDATE,ENDDATE,REPORTTYPE,ACCSTACODE,
    # ACCSTANAME,CUR,TOTINC,SECUSPRI,STKTRAS,BDTRAS,CONVBDINCO,ACINTE,FEERETU,OTHER,TOTALFEE,FDMANAINCO,MERITPAY,
    # FDCUSTFEE,REPTRAFEE,REPINTEFEE,SELLSERF,LISTANNFEE,LISTREFLLOWRTI,OTHERSPRI,INVETOTI,STKINVE,BDINVEINCO,CONVBDPRO,
    # OTHERINVEINCO,REPINCO,OTHERINCOTOT,BKDEINTE,ISSFEEBALA,PURFEE,ISSCOORFEE,ACCOFEE,LAWYFEE,BANKFEE,HMFEE,INFOPUBFEE,
    # BROKFEE,EXCHBONUSFEE,ISSBALA,EXPOBALA,BDACCMAIFEE,OTHERPAY,EXCHTRADEFEE,OTHERFEE,FDNETI,AVGFDNETINC,UNREVALA,FDRESO,
    # FINANETIN,SUBFDLOSE,REDFDLOSE,PROLEQUP,CDISNETINCR,ADISNETI,PROFLOSSADJU,FINANETI,ISSTAT
    # from TQ_FD_PROFIT limit %s, 1000;''')

    TABLE = 'fund_income_statement'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'begin_date':False, 'end_date':False, 'report_type':True} # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'begin_date': None,
        'end_date': None,
    }

    MAP_FIELDS = FundIncomeStatementMap
    MAP_VALUES = FundIncomeStatementValueMap


class FundIncomeStatementNew(DataTableTransfer):
    """
    基金利润表(新准则)(TQ_FD_PROFITNEW) --> 基金利润表(新准则)(fund_income_statement_new)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,BEGINDATE as begin_date ,ENDDATE as end_date,REPORTTYPE as report_type
         from TQ_FD_PROFITNEW ;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code,replace(f.begin_date,'-','') as begin_date,
        replace(f.end_date,'-','') as end_date, report_type from fund_income_statement_new f
         join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select  concat('FD',SECURITYID) as SECURITYID,PUBLISHDATE,BEGINDATE,ENDDATE,REPORTTYPE,ACCSTACODE,
    # ACCSTANAME,CUR,INC,TOTINTEINCO,DEPOINTEINC,BDINTEINCO,ASSBINTE,BUYFIN,INVETOTR,STKINVE,FDINVEQD,BDINVEINCO,ASSBINVI,
    # DERIINVE,DIVIINVE,VALUECHGLOSS,EXCHGGAIN,OTHERINCO,FEE,MANAFEE,CUSTMAINFEE,CUSTFEE,SELLSERF,TRADEFEE,INTEEXPE,
    # SELLFIN,FINEXPE,OTHERFEE,AUDITFEE,INFOPUBFEE,BANKCHAR,ACCTMAIN,SEATUSEF,LISTANNFEE,OTHER,TOTPROFIT,INCOTAXEXPE,
    # NETPROFIT,ISSTAT,MEMO from TQ_FD_PROFITNEW limit %s, 1000;''')

    TABLE = 'fund_income_statement_new'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'begin_date':False, 'end_date':False, 'report_type':True}  # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'begin_date': None,
        'end_date': None,
    }

    MAP_FIELDS = FundIncomeStatementNewMap
    MAP_VALUES = FundIncomeStatementNewValueMap


class FundNavChange(DataTableTransfer):
    """
    基金净值变动表(旧准则)(TQ_FD_NAVCHG) --> 基金净值变动表(旧准则)(fund_nav_chg)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,a.ENDDATE as end_date,a.REPORTTYPE as report_type
    from TQ_FD_NAVCHG a inner join TQ_OA_STCODE b on a.SECODE=b.SECODE and b.SETYPE in(301,302) ;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code, replace(f.end_date,'-','') as end_date, 
    report_type from fund_nav_chg f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',b.SECURITYID) as SECURITYID,a.PUBLISHDATE,a.BEGINDATE,a.ENDDATE,a.REPORTTYPE,
    # a.ACCSTACODE,a.ACCSTANAME,a.CUR,a.INIFDNAV,a.FDNETINC,a.UNREPROF,a.OPERFDNA,a.PROFLOSSADJU,a.FDSUBINCO,a.FDREDEACCT,
    # a.FDEXPOFEE,a.FDTRADENAVCHG,a.HOLDINCRNAVCHG,a.FINANAV,a.ISSTAT,a.MEMO from TQ_FD_NAVCHG a
    # inner join TQ_OA_STCODE b on a.SECODE=b.SECODE and b.SETYPE in(301,302) limit %s, 1000;''')

    TABLE = 'fund_nav_chg'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'end_date':False, 'report_type':True}  # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'begin_date': None,
        'end_date': None,
    }

    MAP_FIELDS = FundNavChangeMap
    MAP_VALUES = FundNavChangeValueMap


class FundNavChangeNew(DataTableTransfer):
    """
    基金净值变动表(新准则)(TQ_FD_NAVCHGNEW) --> 基金净值变动表(新准则)(fund_nav_chg_new)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,ENDDATE as end_date,REPORTTYPE as report_type,PROJECTCODE as project_code
       from TQ_FD_NAVCHGNEW ;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code, replace(f.end_date,'-','') as end_date, 
     report_type ,project_code from fund_nav_chg_new f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID,PUBLISHDATE,ADJUSTDATE,BEGINDATE,ENDDATE,REPORTTYPE,
    # ACCSTACODE,ACCSTANAME,CUR,PROJECTCODE,PROJNAME,PAIDFD,UNDIPROF,OWNERTOTE,ISSTAT
    # from TQ_FD_NAVCHGNEW limit %s, 1000;''')

    TABLE = 'fund_nav_chg_new'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'end_date':False, 'report_type':True, 'project_code':True}   # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'adjust_date': None,
        'begin_date': None,
        'end_date': None,
    }

    MAP_FIELDS = FundNavChangeNewMap
    MAP_VALUES = FundNavChangeNewValueMap


class FundProfitDist(DataTableTransfer):
    """
    基金利润分配情况表(TQ_FD_FUNDDPROFIT) --> 基金利润分配情况表(fund_profit_dist)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,BEGINDATE as begin_date ,ENDDATE as end_date from TQ_FD_FUNDDPROFIT ;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code,replace(f.begin_date,'-','') as begin_date,
    replace(f.end_date,'-','') as end_date from fund_profit_dist f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code 
    where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID,DECLAREDATE,BEGINDATE,ENDDATE,DIVIDENDS,CASHPAYMENT,
    # REINVESTMENT,ADISTRIBUTION,REINVFUNDS, TRANSAMOUNT,PROFITCHANGES,ISSTAT from TQ_FD_FUNDDPROFIT limit %s, 1000;''')

    TABLE = 'fund_profit_dist'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'begin_date':False, 'end_date':False}  # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'begin_date': None,
        'end_date': None,
    }

    MAP_FIELDS = FundProfitDistMap
    MAP_VALUES = FundProfitDistValueMap


class FundUndistProfitNew(DataTableTransfer):
    """
    基金未分配利润表(新准则)(TQ_FD_UNPROFITNEW) --> 基金未分配利润表(新准则)(fund_undist_profit_new)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,ENDDATE as end_date,REPORTTYPE as report_type,PROJECTCODE as project_code
     from TQ_FD_UNPROFITNEW ;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code, replace(f.end_date,'-','') as end_date, 
        report_type ,project_code from fund_undist_profit_new f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID,PUBLISHDATE,ADJUSTDATE,BEGINDATE,ENDDATE,REPORTTYPE,
    # ACCSTACODE,ACCSTANAME,CUR,PROJECTCODE,PROJNAME,ACHIPORTION,UNREALPORTION,TOTUNALLOPROFIT,ISSTAT
    # from TQ_FD_UNPROFITNEW limit %s, 1000;''')

    TABLE = 'fund_undist_profit_new'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'end_date':False, 'report_type':True, 'project_code':True}   # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'adjust_date': None,
        'begin_date': None,
        'end_date': None,
    }

    MAP_FIELDS = FundUndistProfitNewMap
    MAP_VALUES = FundUndistProfitNewValueMap


class FundFinancialNote(DataTableTransfer):
    """
    基金财务附注表(TQ_FD_FUNDFINN) -->	 基金财务附注(fund_financial_note)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,ENDDATE as end_date,SUBJECTCODE as subject_code
         from TQ_FD_FUNDFINN;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code, replace(f.end_date,'-','') as end_date, 
     subject_code from fund_financial_note f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID,DECLAREDATE,BEGINDATE,ENDDATE,REPORTTYPE,CUR,UNIT,
    # SUBJECTTYPE,SUBJECTCODE,SUBJECTNAME,SUBNAMEDECL,AMOUNT,ISSTAT,MEMO
    # from TQ_FD_FUNDFINN limit %s, 1000;''')

    TABLE = 'fund_financial_note'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'end_date':False, 'subject_code':True}   # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'begin_date': None,
        'end_date': None,
    }

    MAP_FIELDS = FundFinancialNoteMap
    MAP_VALUES = FundFinancialNoteValueMap


if __name__ == '__main__':

    log_path = '../logs/export_cwk_server.log'
    logging.basicConfig(filename=log_path,
                        format='%(asctime)s %(name)s %(levelname)s [line %(lineno)s]: %(message)s',
                        filemode='w',
                        level=logging.INFO)

    # FMFIndex.work()
    # FundBalanceSheet.work()
    # FundBalanceSheetNew.work()
    # FundIncomeStatement.work()
    # FundIncomeStatementNew.work()
    # FundNavChange.work()
    # FundNavChangeNew.work()
    # FundProfitDist.work()
    FundUndistProfitNew.work()
    # FundFinancialNote.work()
